﻿using Message;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace Server.Net
{
    /// <summary>
    /// 数据管理
    /// </summary>
    public class DataMgr
    {
        MySqlConnection sqlConn;

        /// <summary>
        /// 单例
        /// </summary>
        public static DataMgr Instance;
        public DataMgr()
        {
            Instance = this;
            Connect();
        }

        //连接
        public void Connect()
        {
            //数据库
            string connStr = "server=119.23.39.55;user=Unity;";
            connStr += "database=ghol;password=Unity/521;port=3306;";
            sqlConn = new MySqlConnection(connStr);
            try
            {
                //sqlConn.Open();
                //Console.WriteLine("[数据库]Connect " + "成功");
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]Connect " + e.Message);
                return;
            }
        }

        //判定安全字符串
        public bool IsSafeStr(string str)
        {
            return !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\}|\{|%|@|\*|!|\']");
        }

        /// <summary>
        /// 是否存在该用户
        /// </summary>
        /// <returns><c>true</c>, if register was caned, <c>false</c> otherwise.</returns>
        /// <param name="uname">Uname.</param>
        private bool CanRegister(string uname)
        {
            //防sql注入
            if (!IsSafeStr(uname))
                return false;
            sqlConn.Open();
            //查询id是否存在
            string cmdStr = string.Format("select * from jh_user where uname='{0}';", uname);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool hasRows = dataReader.HasRows;
                dataReader.Close();
                sqlConn.Close();
                return !hasRows;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]CanRegister fail " + e.Message);
                sqlConn.Close();
                return false;
            }
        }

        /// <summary>
        /// 注册
        /// </summary>
        /// <returns>The register.</returns>
        /// <param name="uname">Uname.</param>
        /// <param name="pwd">Pwd.</param>
        public bool Register(string uname, string pwd)
        {
            //防sql注入
            if (!IsSafeStr(uname) || !IsSafeStr(pwd))
            {
                Console.WriteLine("[数据库]Register 使用非法字符");
                return false;
            }
            //能否注册
            if (!CanRegister(uname))
            {
                Console.WriteLine("[数据库]Register !CanRegister");
                return false;
            }
            sqlConn.Open();
            //写入数据库User表
            string cmdStr = string.Format("insert into jh_user set uname ='{0}' ,pwd ='{1}';", uname, pwd);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                cmd.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]Register " + e.Message);
                sqlConn.Close();
                return false;
            }

        }

        /// <summary>
        /// 创建角色
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool CreatRole(int uid, string name, int type)
        {
            //防sql注入
            if (!IsSafeStr(name))
            {
                Console.WriteLine("[数据库]CreatRole 使用非法字符");
                return false;
            }

            sqlConn.Open();
            //写入数据库User表
            string cmdStr = string.Format("insert into jh_role set uid ='{0}' , name ='{1}' ,type ='{2}' ,level ='{3}';", uid, name, type, 0);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                cmd.ExecuteNonQuery();
                sqlConn.Close();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]CreatRole " + e.Message);
                sqlConn.Close();
                return false;
            }
        }

        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <returns>The roles.</returns>
        /// <param name="uid">Uid.</param>
        public List<Role> GetRoles(int uid)
        {
            sqlConn.Open();
            List<Role> roles = new List<Role>();
            string cmdStr = string.Format("select * from jh_role where uid ='{0}';", uid);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            MySqlDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Role role = new Role
                    {
                        rid = reader.GetInt32(0),
                        uid = reader.GetInt32(1),
                        name = reader.GetString(2),
                        type = reader.GetInt32(3),
                        level = reader.GetInt32(4)
                    };
                    roles.Add(role);
                }
                sqlConn.Close();
                return roles;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]GetRoles " + e.Message);
                sqlConn.Close();
                return roles;
            }
        }

        /// <summary>
        /// 检测用户名密码,返回uid 或-1
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int CheckPassWord(string uname, string pwd)
        {
            int uid = -1;
            //防sql注入
            if (!IsSafeStr(uname) || !IsSafeStr(pwd))
                return uid;
            sqlConn.Open();
            //查询
            string cmdStr = string.Format("select * from jh_user where uname='{0}' and pwd='{1}';", uname, pwd);
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool hasRows = dataReader.HasRows;
                if (hasRows)
                {
                    if (dataReader.Read())
                    {
                        uid = dataReader.GetInt32(0);
                    }

                }
                dataReader.Close();
                sqlConn.Close();
                return uid;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库]CheckPassWord " + e.Message);
                sqlConn.Close();
                return uid;
            }
        }

    }
}
